package com.moorecat.app.provider;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.moorecat.bean.Shop;
import com.moorecat.bean.ShopAddress;

public class DBHelper {

	private static final String TAG = "DBHelper";// 调试标签

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "moore.db";// 数据库名

	private static final String TABLE_NAME = "recent_records";

	private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
			+ " (" + "_ID INTEGER PRIMARY KEY autoincrement,"
			+ "SHOP_ID INTEGER UNIQUE," + "NAME TEXT," + "RATING DOUB,"
			+ "TYPE TEXT," + "AVG DOUB," + "ISCOUPON BOOLEAN," + "AREA TEXT,"
			+ "ADDRESS TEXT," + "TELEPHONE TEXT,LOGO TEXT," + "LAT DOUB,"
			+ "LNG DOUB);";

	SQLiteDatabase db;
	Context context;// 应用环境上下文 Activity 是其子类
	MyDatabaseHelper myDbHelper;

	public DBHelper(Context _context) {
		context = _context;
		myDbHelper = new MyDatabaseHelper(_context);

		// 开启数据库
		// db = context.openOrCreateDatabase(DATABASE_NAME,
		// Context.MODE_PRIVATE,
		// null);
		// CreateTable();
		// Log.v(TAG, "db path=" + db.getPath());
	}

	/**
	 * 建表 列名 区分大小写？ 都有什么数据类型？ SQLite 3 TEXT 文本 NUMERIC 数值 INTEGER 整型 REAL 小数
	 * NONE 无类型 查询可否发送select ?
	 */
	public void CreateTable() {
		try {
			db.execSQL(CREATE_TABLE);
			Log.v(TAG, "Create Table recent_records ok");
		} catch (Exception e) {
			Log.v(TAG, "Create Table recent_records err,table exists.");
		}
	}

	/**
	 * 打开数据库
	 * 
	 * @return
	 * @throws SQLException
	 */
	public DBHelper open() throws SQLException {
		db = myDbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * 关闭数据库
	 */
	public void close() {
		myDbHelper.close();
	}

	/**
	 * 增加数据
	 * 
	 * @param id
	 * @param uname
	 * @return
	 */
	public boolean save(Shop shop) {
		open();
		String sql = "";
		try {
			sql = "insert into " + TABLE_NAME + " values(null," + shop.getId()
					+ ",'" + shop.getName() + "'," + shop.getRating() + ",'"
					+ shop.getCategoryName() + "'," + shop.getAvgConsumption()
					+ ",'" + shop.isHasCoupon() + "','"
					+ shop.getAddress().getAreaName() + "','"
					+ shop.getAddress().getAddress1() + "','" + shop.getPhone()
					+ "','" + shop.getLogo() + "',"
					+ shop.getAddress().getLatitude() + ","
					+ shop.getAddress().getLongitude() + ")";
			db.execSQL(sql);
			Log.v(TAG, "insert Table " + TABLE_NAME + " ok");
			return true;

		} catch (Exception e) {
			Log.v(TAG, "insert Table " + TABLE_NAME + " err ,sql: " + sql);
			return false;
		} finally {
			close();
		}
	}

	public List<Shop> selectAll() {
		open();
		List<Shop> list = new ArrayList<Shop>();
		try {
			Cursor cursor = db.query(TABLE_NAME, new String[] { "_id",
					"shop_id", "name", "rating", "type", "avg", "iscoupon",
					"area", "address", "telephone", "logo", "lat", "lng" },
					null, null, null, null, "_ID DESC");
			if (cursor.getCount() > 0) {
				while (cursor.moveToNext()) {
					Shop shop = new Shop();
					ShopAddress address = new ShopAddress();
					shop.setId(cursor.getInt(1));
					shop.setName(cursor.getString(2));
					shop.setRating(cursor.getDouble(3));
					shop.setCategoryName(cursor.getString(4));
					shop.setAvgConsumption(cursor.getDouble(5));
					if ("true".equals(cursor.getString(6))) {
						shop.setHasCoupon(true);
					} else {
						shop.setHasCoupon(false);
					}
					address.setAreaName(cursor.getString(7));
					address.setAddress1(cursor.getString(8));
					shop.setPhone(cursor.getString(9));
					shop.setLogo(cursor.getString(10));
					address.setLatitude(cursor.getDouble(11));
					address.setLongitude(cursor.getDouble(12));
					shop.setAddress(address);
					list.add(shop);
				}
			} else {
				Log.i(TAG, "select * from " + TABLE_NAME + " error");
			}
			cursor.close();
		} catch (Exception e) {
			Log.e(TAG, "", e);
			e.printStackTrace();
		} finally {
			close();
		}
		return list;
	}

	private static class MyDatabaseHelper extends SQLiteOpenHelper {

		MyDatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "create table " + TABLE_NAME);
			db.execSQL(CREATE_TABLE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.i(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + " , which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
			onCreate(db);
		}

	}

}
